package homework.jdbc.dao;

import homework.jdbc.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/9/13.
 */
//操作User表的
public class UserDao {

    public boolean insert(User user){
        Connection conn=JDBCUtils.getConnection();
        String sql="insert into user(id,username,password) values(?,?,?)";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            int num=ps.executeUpdate();
            if(num==1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(conn!=null){
                JDBCUtils.close();
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return false;
    }

    public boolean delete(int id){
        Connection conn=JDBCUtils.getConnection();
        ResultSet result=null;

        PreparedStatement ps=null;
        try {
            String sql="delete from user where id=?";
            ps = conn.prepareStatement(sql);

            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            int num=ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            if(result!=null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn!=null){
                JDBCUtils.close();
            }
        }
        return false;
    }

    public boolean update(){
        String sql = "update user set username=?,password=? where id=?";
        return false;
    }

    public List<User> findAll() {
        //获得数据库连接
        Connection conn = JDBCUtils.getConnection();
        //创建一个集合，用于将对象添加进行
        List<User> list = new ArrayList<>();
        ResultSet result=null;
        Statement statement = null;
        //以下执行
        try {
            statement = conn.createStatement();
            String sql= "select * from user";
            result = statement.executeQuery(sql);
            while(result.next()){
                User user =new User();
                user.setId(result.getInt("id"));
                user.setUsername(result.getString("username"));
                user.setPassword(result.getString("password"));

                list.add(user);
            }
            return list;
        } catch(SQLException e){
            e.printStackTrace();
        }finally {
            //关闭资源
            if(result!=null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }



    public User findById(int id){//有//的代码是根据83行id=数字进行查找的，没有//的是预处理，根据输入想要的id进行查找
        //获得数据库连接
        Connection conn = JDBCUtils.getConnection();
        ResultSet result=null;
        //Statement statement = null;
        User user=new User();
        PreparedStatement ps=null;
        //以下执行
        try {
            //statement = conn.createStatement();
            //编写sql语句，？是占位符
            String sql= "select * from user where id=?";
            //sql语句交给ps进行预处理
            ps=conn.prepareStatement(sql);
            //设置sql语句的参数
            ps.setInt(1,id);
            result =ps.executeQuery();
            //result = statement.executeQuery(sql);
            while(result.next()){
                user.setId(result.getInt("id"));
                user.setUsername(result.getString("username"));
                user.setPassword(result.getString("password"));
                return user;
            }
        } catch(SQLException e){
            e.printStackTrace();
        }finally {
            //关闭资源
            if(result!=null){
                try {
                    result.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            /*if(statement!=null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }*/
         if(conn!=null){
                JDBCUtils.close();
         }
        }
        return null;
    }
}
